package com.tengju.bff.interfaces.covercharge.util;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取Excel工具
 *
 * @author zhaoyi
 */
public class ExcelReadUtil {

    /***
     * 获取工作Workbook
     *
     * @return
     */
    public static Workbook getWorkbook(InputStream inputStream, boolean flag) {
        Workbook workBook = null;
        try {
            if (flag) {
                workBook = new HSSFWorkbook(inputStream);
            } else {
                workBook = new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

    /***
     * 获取工作Workbook
     *
     * @return
     */
    @SuppressWarnings("deprecation")
    public static Workbook getWorkbook(String filePath, boolean flag) {
        Workbook workBook = null;
        try {
            if (flag) {
                File f = new File(filePath);
                FileInputStream is = new FileInputStream(f);
                POIFSFileSystem fs = new POIFSFileSystem(is);
                workBook = new HSSFWorkbook(fs);
                is.close();
            } else {
                workBook = new XSSFWorkbook(filePath);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

    /**
     * 根据excel表的索引获取工作表
     *
     * @param workBook   excel文件
     * @param sheelIndex 工作表的索引
     * @return
     */
    public static Sheet getSheetByIndex(Workbook workBook, int sheelIndex) {
        Sheet sheet = null;
        if (workBook != null) {
            try {
                sheet = workBook.getSheetAt(sheelIndex);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sheet;
    }

    /**
     * 根据excel表的名称获取工作表
     *
     * @param workBook
     * @param sheelIndex
     * @return
     */
    public static Sheet getSheetByName(Workbook workBook, String sheelName) {
        Sheet sheet = null;
        if (workBook != null) {
            try {
                sheet = workBook.getSheet(sheelName);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sheet;
    }

    /***
     * 获取excel所有工作表
     *
     * @param workBook
     * @return
     */
    public static List<Sheet> getAllSheet(Workbook workBook) {
        List<Sheet> list = null;
        if (workBook != null) {
            try {
                if (workBook != null) {
                    if (workBook.getNumberOfSheets() > 0) {
                        list = new ArrayList<Sheet>();
                        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
                            list.add(workBook.getSheetAt(i));
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /***
     * 获取某一行
     *
     * @param workBook
     * @param rowIndex
     * @return
     */
    public static Row getRowByIndex(Sheet sheet, int rowIndex) {
        Row row = null;
        try {
            if (sheet != null) {
                if (rowIndex > 0 && rowIndex <= (sheet.getLastRowNum() + 1)) {
                    row = sheet.getRow(rowIndex - 1);
                } else {
                    return null;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 获取所有行
     *
     * @param sheet
     * @return
     */
    public static List<Row> getAllRow(Sheet sheet) {
        List<Row> rows = null;
        try {
            if (sheet != null) {
                if (sheet.getLastRowNum() > 0) {
                    rows = new ArrayList<Row>();
                    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }

    /**
     * 获取工作表的哪几行
     *
     * @param HSSFSheet
     * @param start     开始位置
     * @param end       结束位置
     * @return 集合
     */
    public static List<Row> getRowByStartAndEnd(Sheet sheet, String sstart, String send) {
        int start = Integer.parseInt(sstart == null ? "0" : sstart);
        int end = Integer.parseInt(send == null ? "0" : send);
        List<Row> rows = null;
        try {
            if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
                rows = new ArrayList<Row>();
                if (start == 0 && end == 0) {
                    for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                } else if (start <= sheet.getPhysicalNumberOfRows() + 1 && end == 0
                        && start > 0) {
                    for (int i = start - 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                } else if (start <= sheet.getPhysicalNumberOfRows() + 1 && end > 0
                        && end <= sheet.getPhysicalNumberOfRows() + 1 && end >= start
                        && start > 0) {
                    for (int i = start - 1; i <= end - 1; i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                } else if (start > 0 && end <= sheet.getPhysicalNumberOfRows() + 1
                        && start <= sheet.getPhysicalNumberOfRows() + 1
                        && start == end) {
                    for (int i = start - 1; i <= end - 1; i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                } else if (start > end) {
                    for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                } else {
                    for (int i = start; i <= sheet.getPhysicalNumberOfRows(); i++) {
                        if (sheet.getRow(i) != null) {
                            rows.add(sheet.getRow(i));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return rows;
    }

    /***
     * 获取某一行的所有单元格
     *
     * @param HSSFsheet
     * @param rowIndex
     * @return
     */
    public static List<Cell> getOneRowCells(Sheet sheet, int rowIndex) {
        List<Cell> cells = null;
        try {
            Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
            if (row != null) {
                if (row.getLastCellNum() > 0) {
                    cells = new ArrayList<Cell>();
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        cells.add(row.getCell((short) i));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cells;
    }

    /***
     * 获取某一行的所有单元格的内容
     *
     * @param HSSFsheet
     * @param rowIndex
     * @return
     */
    public static String[] getOneRowCellsContext(Sheet sheet, int rowIndex) {
        String[] cells = null;

        try {
            Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
            if (row != null) {
                if (row.getLastCellNum() > 0) {
                    cells = new String[row.getLastCellNum()];
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        cells[i] = ExcelReadUtil.getCellContent(row
                                .getCell((short) i));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cells;

    }

    /***
     * 获取某一行的所有单元格
     * @param inputstream
     * @param rowIndex
     * @param sheelIndex
     * @return
     */
    public static String[] getOneRowCellsContextIs(InputStream inputstream, boolean flag, int rowIndex, int sheelIndex) {
        Workbook workBook = ExcelReadUtil.getWorkbook(inputstream, flag);
        return ExcelReadUtil.getOneRowCellsContext(ExcelReadUtil.getSheetByIndex(workBook, sheelIndex), rowIndex);
    }

    /**
     * 获取某一行第几个单元格
     *
     * @param sheet
     * @param rowIndex  行索引
     * @param cellIndex 列索引
     * @return 单元格对象
     */
    public static Cell getOneCell(Sheet sheet, int rowIndex,
                                  int cellIndex) {
        Cell cell = null;
        try {
            Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
            if (row != null && cellIndex > 0 && cellIndex <= row.getLastCellNum()) {
                cell = row.getCell((short) (cellIndex - 1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cell;
    }

    /*****
     * 获取哪几行的哪几个单元格
     *
     * @param sheet
     * @param scellstart
     * @param scellend
     * @param srowstart
     * @param srowend
     * @return
     */
    public static List<Cell> getCellsByStartAndEnd(Sheet sheet,
                                                   String scellstart, String scellend, String srowstart, String srowend) {
        int cellstart = Integer.parseInt(scellstart == null ? "0" : scellstart);
        int cellend = Integer.parseInt(scellend == null ? "0" : scellend);
        List<Cell> cells = null;
        List<Row> rows = ExcelReadUtil.getRowByStartAndEnd(sheet, srowstart, srowend);
        try {
            if (rows != null && rows.size() > 0) {
                cells = new ArrayList<Cell>();
                for (int i = 0; i < rows.size(); i++) {
                    Row row = rows.get(i);
                    if (cellstart == 0 && cellend == 0) {
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            cells.add(row.getCell((short) j));
                        }
                    } else if (cellstart > 0 && cellend <= row.getLastCellNum()
                            && cellend > 0 && cellstart <= cellend) {
                        for (int j = cellstart - 1; j <= (cellend - 1); j++) {
                            cells.add(row.getCell((short) j));
                        }

                    } else if (cellstart > 0
                            && cellstart <= row.getLastCellNum()
                            && cellend == 0) {
                        for (int j = cellstart - 1; j < row.getLastCellNum(); j++) {
                            cells.add(row.getCell((short) j));
                        }
                    } else {
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            cells.add(row.getCell((short) j));
                        }
                    }
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return cells;
    }

    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String getCellContent(Cell cell) {
        String str = "";

        int type = cell.getCellType();
        switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    str = DateTimeUtils.parseDateAsString(cell.getDateCellValue(), "yyyyMMddHHmmss");
                } else {
                    DecimalFormat df = new DecimalFormat("0.00");
                    str = killzero(df.format(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                str = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_STRING:
                str = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                str = "";
            default:
                break;
        }
        return str;
    }

    /***
     * 获取指定单元格内容
     *
     * @param HSSFsheet
     * @param scellstart
     * @param scellend
     * @param srowstart
     * @param srowend
     * @return
     */
    public static List<String[]> getCellsByStartAndEndContext(
            Sheet sheet, String scellstart, String scellend,
            String srowstart, String srowend) {
        int cellstart = Integer.parseInt(scellstart == null ? "0" : scellstart);
        int cellend = Integer.parseInt(scellend == null ? "0" : scellend);
        List<String[]> cells = new ArrayList<String[]>();
        List<Row> rows = ExcelReadUtil.getRowByStartAndEnd(sheet, srowstart, srowend);
        try {
            if (rows != null && rows.size() > 0) {
                String[] str = null;
                for (int i = 0; i < rows.size(); i++) {

                    Row row = rows.get(i);
                    if (cellstart == 0 && cellend == 0) {
                        int s = 0;
                        str = new String[row.getLastCellNum()];
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }

                            s++;
                        }
                    } else if (cellstart > 0 && cellend <= row.getLastCellNum()
                            && cellend > 0 && cellstart <= cellend) {
                        int s = 0;
                        str = new String[((cellend - cellstart) < 0 ? 0 : (cellend - cellstart)) + 1];
                        for (int j = cellstart - 1; j <= (cellend - 1); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    } else if (cellstart > 0
                            && cellstart <= row.getLastCellNum()
                            && cellend == 0) {
                        int s = 0;
                        str = new String[row.getLastCellNum() - cellstart + 1];
                        for (int j = cellstart - 1; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    } else {
                        int s = 0;
                        str = new String[row.getLastCellNum()];
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    }
                    cells.add(str);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return cells;
    }


    /***
     * 获取指定单元格内容
     *
     * @param HSSFsheet
     * @param scellstart
     * @param scellend
     * @param srowstart
     * @param srowend
     * @return
     */
    public static List<ExcelReadDTO> getCellsByStartAndEndContext2(
            Sheet sheet, String scellstart, String scellend,
            String srowstart, String srowend) {
        int cellstart = Integer.parseInt(scellstart == null ? "0" : scellstart);
        int cellend = Integer.parseInt(scellend == null ? "0" : scellend);
        List<ExcelReadDTO> cells = Lists.newArrayList();
        List<Row> rows = ExcelReadUtil.getRowByStartAndEnd(sheet, srowstart, srowend);
        try {
            if (rows != null && rows.size() > 0) {
                ExcelReadDTO readDTO = new ExcelReadDTO();
                String[] str = null;
                for (int i = 0; i < rows.size(); i++) {
                    Row row = rows.get(i);
                    if (cellstart == 0 && cellend == 0) {
                        int s = 0;
                        str = new String[row.getLastCellNum()];
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }

                            s++;
                        }
                    } else if (cellstart > 0 && cellend <= row.getLastCellNum()
                            && cellend > 0 && cellstart <= cellend) {
                        int s = 0;
                        str = new String[((cellend - cellstart) < 0 ? 0 : (cellend - cellstart)) + 1];
                        for (int j = cellstart - 1; j <= (cellend - 1); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    } else if (cellstart > 0
                            && cellstart <= row.getLastCellNum()
                            && cellend == 0) {
                        int s = 0;
                        str = new String[row.getLastCellNum() - cellstart + 1];
                        for (int j = cellstart - 1; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    } else {
                        int s = 0;
                        str = new String[row.getLastCellNum()];
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            if (row.getCell(j) != null) {
                                str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
                            } else {
                                str[s] = "";
                            }
                            s++;
                        }
                    }
                    readDTO.setLine(i);
                    readDTO.setContent(str);
                    cells.add(readDTO);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return cells;
    }

    /***
     * 解析excel
     *
     * @param inputstream  excel数据流
     * @param scellstart   开始单元格为NULL时默认从0格开始
     * @param scellend     每一行的结束单元格为Null表示最后格
     * @param srowstart    开始行
     * @param srowend      结束行
     * @param sheelIndex
     * @return
     */
    public static List<String[]> getAllExcelInfo(InputStream inputstream, boolean flag,
                                                 String scellstart, String scellend, String srowstart,
                                                 String srowend, int sheelIndex) {
        Workbook workBook = ExcelReadUtil.getWorkbook(inputstream, flag);
        return ExcelReadUtil.getCellsByStartAndEndContext(
                ExcelReadUtil.getSheetByIndex(workBook, sheelIndex),
                scellstart, scellend, srowstart, srowend);
    }

    /***
     * 解析excel
     *
     * @param inputstream  excel数据流
     * @param scellstart   开始单元格为NULL时默认从0格开始
     * @param scellend     每一行的结束单元格为Null表示最后格
     * @param srowstart    开始行
     * @param srowend      结束行
     * @param sheelIndex
     * @return
     */
    public static List<ExcelReadDTO> getAllExcelInfo2(InputStream inputstream, boolean flag,
                                                 String scellstart, String scellend, String srowstart,
                                                 String srowend, int sheelIndex) {
        Workbook workBook = ExcelReadUtil.getWorkbook(inputstream, flag);
        return ExcelReadUtil.getCellsByStartAndEndContext2(
                ExcelReadUtil.getSheetByIndex(workBook, sheelIndex),
                scellstart, scellend, srowstart, srowend);
    }

    /***
     * 解析excel
     * @param filepath
     * @param scellstart
     * @param scellend
     * @param srowstart
     * @param srowend
     * @param sheelIndex
     * @return
     */
    public static List<String[]> getAllExcelInfo(String filepath, boolean flag,
                                                 String scellstart, String scellend, String srowstart,
                                                 String srowend, int sheelIndex) {

        Workbook workBook = ExcelReadUtil.getWorkbook(filepath, flag);
        return ExcelReadUtil.getCellsByStartAndEndContext(
                ExcelReadUtil.getSheetByIndex(workBook, sheelIndex),
                scellstart, scellend, srowstart, srowend);
    }

    /**
     * 去掉没有意义小数位0
     *
     * @param str
     * @return
     */
    public static String killzero(final String str) {
        if (StringUtils.isBlank(str)) {
            return "";
        }

        String resultStr = str;
        while (resultStr.indexOf(".") != -1
                && resultStr.charAt(resultStr.length() - 1) == '0') {
            resultStr = resultStr.substring(0, resultStr.length() - 1);
        }

        if (resultStr.charAt(resultStr.length() - 1) == '.') {
            resultStr = resultStr.substring(0, resultStr.length() - 1);
        }
        return resultStr;
    }

    public static void main(String[] args) throws Exception {
        InputStream fis = new FileInputStream(new File("F:\\对账模板.xlsx"));
        List<String[]> cells = ExcelReadUtil.getAllExcelInfo(fis, false, null, null, "2", null, 0);
        for (int j = 0; j < cells.size(); j++) {
            for (int i = 0; i < cells.get(j).length; i++) {
                System.out.println(cells.get(j)[i]);

            }
            System.out.println();
        }
    }
}